use warnings;
use strict;

#use Win32::ODBC;
use DBI;
use DBD::ODBC;

use Audio::Beep;

use IO::Handle;  # For autoflush

sub check_new_order()
{

  # the beeper for warning for new order(s)
  my $beeper = Audio::Beep->new();

                # lilypond subset syntax accepted
                # relative notation is the default 
                # (now correctly implemented)
  my $music = "g' f bes' c8 f d4 c8 f d4 bes c g f2";

  my $database = "sq_yulancan";
  my $host = "202.75.219.219";
  my $port = "1433";
  my $uid = "sq_yulancan";
  my $pwd = "yulancan";

  #print "CONNECTIONG TO db ...\n";
  # DATA SOURCE NAME
  # my $dsn = "dbi:ODBC:$database:$host:$port";
  my $dsn = "dbi:ODBC:$database:$host";;

  my $conn_string = "driver={SQL Server};Server=$host;Database=$database;UID=$uid;PWD=$pwd";

  my $dbh = DBI->connect( "DBI:ODBC:$conn_string" ) or die "failed to connect\n";


  #print "get new orders...\n";
  # Check if any new orders there
  my $sqlConsumer = "SELECT orders.id as orders_id FROM orders WHERE status=1";
  my $get_handle = $dbh->prepare($sqlConsumer) or die "Failed to prepare($sqlConsumer)\n";
  $get_handle->execute();

  my $orders_id;
  $get_handle->bind_columns(undef, \$orders_id);

  my @order_id_array;

  my $counter = 0;
  while($get_handle->fetch())
	{
	 print "[" . ++$counter . "] orders_id = $orders_id\n";
	 push(@order_id_array,$orders_id);
	}
	$get_handle->finish();

  #print "try to display detailed orders...\n";
  for(my $i=0;$i<scalar @order_id_array; $i++)
	{
	  $orders_id = $order_id_array[$i];

	  # update the status of the new order to detected
	  my $sqlUpdateNewOrder = "UPDATE orders set status=2 where id=".$orders_id;
	  $dbh->do($sqlUpdateNewOrder) or die "Failed to update order's status for $orders_id\n";
	  
	  print "==========[$orders_id]================\n";

	 # display the details of the order
	 my $sqlOrderDetail = "SELECT consumer.phone as consumer_mobile,district.name as district_name,building,door,dish.name as dish_name,price,restaurant.name as provider ".
						 " FROM orders,consumer,address,district,position,restaurant,orders_category_dish,CategoryDish,dish ".
						 " WHERE orders.id=$orders_id and consumer_id=consumer.id and address_id=address.id and district_id=district.id and position_id=position.id ".
						"  and orders.id=orders_id and restaurant_id=restaurant.id and categorydish_id=CategoryDish.id and dish_id=dish.id;";
	 # print "$sqlOrderDetail\n";
	 my $qhandle = $dbh->prepare($sqlOrderDetail) or die "Failed to prepare sqlOrderDetail\n";
	 my $consumer_mobile;
	 my $district_name;
	 my $building;
	 my $door;
	 my $dish_name;
	 my $price;
	 my $provider;

	 $qhandle->execute();
	 # my $sth = $qhandle->execute();
	 $qhandle->bind_columns(undef,\$consumer_mobile,\$district_name,\$building,\$door,\$dish_name,\$price,\$provider);

	 my $counter2=0;
	 while ($qhandle->fetch()) {
		 
		 print "[".++$counter2."]"."$consumer_mobile\t$district_name\t$building#$door room\t$dish_name\t$price\t$provider\n";

		 # warning the admin by ring.
		 $beeper->play( $music );		 
	 }
	 $qhandle->finish();

	 $dbh->disconnect();
  }

}
sub main()
{
	
	

	my $promotion = "Checking ... ";
	print "watcher started ...\n";
	while (1) {
		print $promotion;
		STDOUT->flush();
		check_new_order();
		$promotion = "Checking ... ";

		# sleep 10 seconds
		sleep(60);
	}

	print "end of watch.\n";
}
main();